package com.iris.live.services.report.sources;

import com.google.common.collect.Maps;
import com.iris.live.services.common.DateFormatUtils;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.models.ReportConfig;
import com.iris.live.services.report.data.BaseReportDataSource;
import com.iris.live.services.report.data.ReportContext;
import org.springframework.stereotype.Component;

import java.text.DecimalFormat;
import java.util.*;

/**
 * Created by HangLiu on 2017/8/12.
 */
@Component("SaleManagementDataSource")
public class SaleManagementDataSource  extends BaseReportDataSource  {
    @Override
    public List getData(ReportContext context, List<ReportConfig> config) throws Exception {
        Map info= context.getParams() ;
        String lineType=info.get("displayType").toString();
        List<Map> datas = null;
        if("1".equals(lineType)){
            datas=  getTimeLineData(info);
        }
        else{
            datas=  getOrgLineData(info);
        }

        super.brandGroup(context ,datas);
        return  datas;
    }

    private List getTimeLineData(Map info){
        String sql=" SELECT vehicle_brand 品牌, substring(stat_date, 5,2) date, " + this.getIndicatorColumns(info) +
                "    from LSH经销商表 a, t_bi_monthly_stat_data b   " +
                "    where  a.许可_BMBS = b.permit and 1=1 and b.stat_date >= date_format(:startDay, '%Y%m') and b.stat_date <= date_format(:endDay, '%Y%m') " +
                "    group by 品牌, date ";

        Map map = Maps.newHashMap();
        String permit = info.get("permit") == null ? "" : info.get("permit").toString();
        if(! StringUtils.isNullOrBlank(permit) && !"LSH".equals(permit)){
            sql= sql.replace("1=1","  (a.大区= :permit  or  a.大区_EN=:permit or a.CTA=:permit or  a.CTA_EN=:permit    or b.permit=:permit) ");
            map.put("permit", info.get("permit"));
        }
        map.put("startDay",info.get("startDay"));
        map.put("endDay", info.get("endDay"));
        List resultSet = this.getList(sql,map);
        return resultSet;
    }

    private List getOrgLineData(Map info)  throws Exception{
        String sql = "select  'DCH' 集团,a.许可_LSH,a.大区 AS 大区, a.大区_EN AS 大区_EN, a.小区 AS 小区, a.小区_EN AS 小区_EN, " +
                "    IF ( isnull(a.CTA) OR a.CTA = '', '其他cta', a.CTA ) AS CTA,  " +
                "    IF (isnull(a.CTA_EN) OR a.CTA_EN = '', 'Other CTA', a.CTA_EN ) AS CTA_EN, " +
                "    a.许可_BMBS AS 许可_BMBS, " +
                "    a.经销商名 AS 经销商名, " +
                "    a.经销商名_EN AS 经销商名_EN, " +
                "    a.SalesCode AS salesCode, b.vehicle_brand 品牌, " +
                this.getIndicatorColumns(info) +
                "    from LSH经销商表 a, t_bi_monthly_stat_data b   " +
                "    where  a.许可_BMBS = b.permit and 1=1 and b.stat_date >= date_format(:startDay, '%Y%m') and b.stat_date <= date_format(:endDay, '%Y%m') " +
                "    group by 品牌, permit ";
        Map map = Maps.newHashMap();

        String endDay = info.get("endDay").toString();
        if (DateFormatUtils.getFormatDate(endDay, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD).after(new Date())) {
            endDay = DateFormatUtils.getFormatDateToString(new Date(), DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD);
        }
        map.put("startDay", info.get("startDay"));
        map.put("endDay", endDay);
        List<Map> datas = this.getList(sql, map);

        //计算三个月平均销量
        String endMonth = endDay.replace("-", "").substring(0, 6), startMonth = null;
        int monthNum = 3;
        if (endMonth.substring(4).equals("01")) {
            startMonth = endDay.substring(0, 4) + "01";
            monthNum = 1;
        } else if (endMonth.substring(4).equals("02")) {
            startMonth = endDay.substring(0, 4) + "01";
            monthNum = 2;
        } else {
            startMonth = String.valueOf(Integer.parseInt(endMonth) - 2);
        }

        map.clear();
        map.put("monthNum", monthNum);
        map.put("startMonth", startMonth);
        map.put("endMonth", endMonth);
        sql = "SELECT permit, vehicle_brand 品牌, SUM(vehicle_sale_volumn) 三个月销量, round(SUM(vehicle_sale_volumn)/:monthNum,0) 三个月平均销量 from t_bi_monthly_stat_data  where stat_date >= :startMonth and stat_date <= :endMonth and permit is not null and vehicle_brand is not null group by 品牌, permit";
        List<Map> threeMonthSale  = this.getList(sql, map);
        for(Map value : threeMonthSale){
            map.put(value.get("permit").toString() + "_" + value.get("品牌").toString(), value);
        }

        String key = null;
        for(Map value : datas){
            key = value.get("许可_BMBS").toString() + "_" + value.get("品牌").toString();
            if(map.containsKey(key)){
                value.put("三个月销量", ((Map)map.get(key)).get("三个月销量"));
                value.put("三个月平均销量", ((Map)map.get(key)).get("三个月平均销量"));
            }else{
                value.put("三个月销量", 0);
                value.put("三个月平均销量", 0);
            }
        }

        return datas;
    }

    private String getIndicatorColumns(Map info){
        StringBuffer  indicators = new StringBuffer();
            // 销量部分
        indicators.append(
            "  SUM(IFNULL(purchase_num_target,0)) 采购签署目标 , " +
            "  SUM(IFNULL(purchase_num,0)) 实际采购,  " +
            "  SUM(IFNULL(manufacturer_sale_target,0)) 零售签署目标, " +
            "  SUM(IFNULL(manufacturer_vehicle_sale_volumn,0)) 实际零售NBI, " +
            "  SUM(IFNULL(dealer_sale_target,0)) ROS零售目标, " +
            "  SUM(IFNULL(vehicle_sale_volumn,0)) 新车销量, " +
            "  ROUND(SUM(IFNULL(manufacturer_vehicle_sale_volumn,0)) / SUM(IFNULL(manufacturer_sale_target,0)),4)  NBI零售完成率, " +
            "  ROUND(SUM(IFNULL(purchase_num,0)) / SUM(IFNULL(purchase_num_target,0)),4)  采购完成率, " +
            "  ROUND(SUM(IFNULL(vehicle_sale_volumn,0)) / SUM(IFNULL(dealer_sale_target,0)),4)  ROS零售完成率, "
        );
                    //  库存部分
        String lineType=info.get("displayType").toString();
        if("1".equals(lineType)){ //时间
            indicators.append("  SUM(IFNULL(stock_num,0)) 期末库存台数,  " +
                    "  SUM(IFNULL(age_larger_120,0) + IFNULL(age_larger_150,0)) 大于120天库存台数, " +
                    "  SUM(IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0)) 60_119天库存台数, " +
                    "  SUM(IFNULL(stock_num,0) - IFNULL(age_larger_60,0) - IFNULL(age_larger_90,0) - IFNULL(age_larger_120,0)  - IFNULL(age_larger_150,0) ) 60天以下库存台数,  " +
                    "  SUM(IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0) + IFNULL(age_larger_120,0)  + IFNULL(age_larger_150,0)) 60天以上长库龄台数,  " +
                    "  ROUND(SUM(IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0) + IFNULL(age_larger_120,0)  + IFNULL(age_larger_150,0)) / SUM(IFNULL(stock_num,0)),6)  60天以上长库龄比例, "
            );
        }else{
            indicators.append("  SUM(IF(stat_date <> date_format(:endDay,'%Y%m'), 0, IFNULL(stock_num,0))) 期末库存台数,  " +
                    "  SUM(IF(stat_date <> date_format(:endDay,'%Y%m'), 0, IFNULL(age_larger_120,0) + IFNULL(age_larger_150,0))) 大于120天库存台数, " +
                    "  SUM(IF(stat_date <> date_format(:endDay,'%Y%m'), 0, IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0))) 60_119天库存台数, " +
                    "  SUM(IF(stat_date <> date_format(:endDay,'%Y%m'), 0, IFNULL(stock_num,0) - IFNULL(age_larger_60,0) - IFNULL(age_larger_90,0) - IFNULL(age_larger_120,0)  - IFNULL(age_larger_150,0))) 60天以下库存台数,  " +
                    "  SUM(IF(stat_date <> date_format(:endDay,'%Y%m'), 0, IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0) + IFNULL(age_larger_120,0)  + IFNULL(age_larger_150,0))) 60天以上长库龄台数,  " +
                    "  ROUND(IF(stat_date <> date_format(:endDay,'%Y%m'), 0,  SUM(IFNULL(age_larger_60,0) + IFNULL(age_larger_90,0) + IFNULL(age_larger_120,0)  + IFNULL(age_larger_150,0)) / SUM(IFNULL(stock_num,0))),6)  60天以上长库龄比例, "
            );
        }

        indicators.append(
            //  财务部分
            "  SUM(IFNULL(vehicle_sale_amount,0)) 新车外部销售收入, " +
            "  SUM(IFNULL(GP1_amount,0)) gpi_price, " +
            "  ROUND(SUM(IFNULL(GP1_amount,0))  / SUM(IFNULL(vehicle_sale_amount,0)),4)  GPI率, " +
            "  SUM(IFNULL(GP1dot5_amount,0))  gpi_5_price, " +
            "  ROUND(SUM(IFNULL(GP1dot5_amount,0))  / SUM(IFNULL(vehicle_sale_amount,0)),4)  GP1dot5率, " +
            "  SUM(IFNULL(gp2_amount,0)) gpii_price, " +
            "  ROUND(SUM(IFNULL(gp2_amount,0))  / SUM(IFNULL(vehicle_sale_amount,0)),4)  GP2率, " +
            "  SUM(IFNULL(si_amount,0)) si_price,"  +
            "  ROUND(SUM(IFNULL(si_amount,0))  / SUM(IFNULL(vehicle_sale_amount,0)),4)  SI率, " +
            "  SUM(IFNULL(gp3_amount,0)) gpiii_price,"  +
            "  ROUND(SUM(IFNULL(gp3_amount,0))  / SUM(IFNULL(vehicle_sale_amount,0)),4)  GP3率, " +

            // 其他部分
            "  SUM(IFNULL(b.after_market_product_amount ,0)) 精品销售价格,  " +
            "  ROUND(SUM(IFNULL(after_market_product_amount,0)) / SUM(IFNULL(vehicle_sale_volumn,0)),2)  后市场产品单车营业额, " +
            "  SUM(IFNULL(b.after_market_product_gross_amount ,0)) 精品毛利不含税, " +
            "  ROUND(SUM(IFNULL(after_market_product_gross_amount,0))  / SUM(IFNULL(after_market_product_amount,0)),4)  后市场产品销售毛利率, " +

            "  SUM(IFNULL(b.extended_warranty_num ,0)) 第三方延保台数,  " +
            "  ROUND(SUM(IFNULL(extended_warranty_num,0))  / SUM(IFNULL(vehicle_sale_volumn,0)),4)  新车延保渗透率, " +
            "  SUM(IFNULL(b.extended_warranty_gross_amount ,0)) 第三方延保毛利不含税, " +
            "  SUM(IFNULL(b.extended_warranty_amount ,0)) 第三方延保营业额, " +
            "  SUM(IFNULL(b.new_insurance_vehicle_num ,0)) 新保台数,  " +
            "  ROUND(SUM(IFNULL(new_insurance_vehicle_num,0)) / SUM(IFNULL(vehicle_sale_volumn,0)),6)  新保渗透率, " +
            "  SUM(IFNULL(b.insurance_commission_amount ,0)) 新保手续费收入, " +
            "  SUM(IFNULL(b.financial_vehicle_num, 0)) 金融台数, " +
            "  ROUND(SUM(IFNULL(financial_vehicle_num,0)) / SUM(IFNULL(vehicle_sale_volumn,0)),6)  金融渗透率, " +
            //"  SUM(IFNULL(b.financial_agility_vehicle_num, 0)) new_car_agility, " +
            "  SUM(IFNULL(b.financial_commission_amount, 0)) 金融手续费, " +
            "  SUM(IFNULL(b.financial_service_fee_amount, 0)) 金融服务费, " +
            "  ROUND(SUM(IFNULL(financial_service_fee_amount,0)) / SUM(IFNULL(financial_vehicle_num,0)),1)  单车金融服务费收入, " +
            "  SUM(IFNULL(b.vehicle_license_service_amount, 0)) 上牌服务费, " +
            "  SUM(IFNULL(b.vehicle_other_service_amount, 0)) 其他服务费, " +
            //其他收入总计 = 后市场产品销售毛利 + 金融服务费收入 + 上牌服务费收入 + 新保手续费收入 + 金融手续费收入 + 五星延保毛利 + 其他服务费收入
            //bug 8043 原sql统计缺少  新保手续费收入，现添加
            "  SUM(IFNULL(after_market_product_gross_amount,0)) + SUM(IFNULL(financial_service_fee_amount,0)) + SUM(IFNULL(financial_commission_amount,0))  + SUM(IFNULL(vehicle_other_service_amount,0))  + SUM(IFNULL(vehicle_license_service_amount,0))   + SUM(IFNULL(extended_warranty_gross_amount,0))    其他收入总计, " +

            "  SUM(IFNULL(gp3_amount,0) + IFNULL(after_market_product_gross_amount,0) + IFNULL(financial_service_fee_amount,0) + IFNULL(financial_commission_amount,0)  + IFNULL(vehicle_other_service_amount,0)  + IFNULL(vehicle_license_service_amount,0) + IFNULL(extended_warranty_gross_amount,0) + IFNULL(insurance_commission_amount,0)) tgp_amount, " +
            "  SUM(IFNULL(vehicle_sale_amount,0) +IFNULL(after_market_product_amount,0) + IFNULL(financial_service_fee_amount,0) + IFNULL(financial_commission_amount,0)  + IFNULL(vehicle_other_service_amount,0)  + IFNULL(vehicle_license_service_amount,0) + IFNULL(extended_warranty_amount,0) + IFNULL(insurance_commission_amount,0)) vehicle_sale_gross_amount, " +
            "  ROUND(SUM(IFNULL(gp3_amount,0) + IFNULL(after_market_product_gross_amount,0) + IFNULL(financial_service_fee_amount,0) + IFNULL(financial_commission_amount,0)  + IFNULL(vehicle_other_service_amount,0)  + IFNULL(vehicle_license_service_amount,0) + IFNULL(extended_warranty_gross_amount,0)  + IFNULL(insurance_commission_amount,0))/ " +
            "  SUM(IFNULL(vehicle_sale_amount,0) + IFNULL(after_market_product_amount,0) + IFNULL(financial_service_fee_amount,0) + IFNULL(financial_commission_amount,0)  + IFNULL(vehicle_other_service_amount,0)  + IFNULL(vehicle_license_service_amount,0) + IFNULL(extended_warranty_amount,0) + IFNULL(insurance_commission_amount,0)), 6) TGP率, " +

            "  SUM(IFNULL(b.sc_num ,0)) 销售部人数, " +
            "  ROUND(SUM(IFNULL(vehicle_sale_volumn,0)) / SUM(IFNULL(sc_num,0)),2) 销售人均生产力 "
        );

       return indicators.toString();
    }
}
